ALTER     proc sp_load_final_inventory_by_partnumber_and_lineitem
(@mfgPartNumber varchar(50), @lineItem_id int, @cslit_id int)
As
declare @process_step_id bigint
declare @process_type varchar(20)
declare @process_type_name varchar(20)
declare @results Table(	Lot_Number varchar(50),
			Fab_lot_Number varchar(50),
			Lot_ID bigint, 
			Location_ID int,
			Location varchar(20),
			Lot_Date_Code varchar(50),
			Step_Name varchar(20),
			Date_In datetime,
			Qty_In int,
			Balance_Qty int,
			Reserved_Qty int,
			Quantity_Pull int)

--declare @temp_cslit_id int
declare @temp_lot_id int
declare @temp_process_step_id bigint
declare @temp_location_id int
declare @temp_lot_number varchar(50)
declare @temp_fab_lot_number varchar(50)
declare @temp_location varchar(20)
declare @temp_lot_date_code varchar(50)
declare @temp_step_name varchar(20)
declare @temp_date_in datetime
declare @temp_qty_in int
declare @temp_bal_qty int
declare @temp_ship_qty int
declare @temp_qty_pull int
declare @tempCursor CURSOR
declare @pfcursor_deallocate_status int
declare @partNumber varchar(50)
declare @reserved_quantity int
declare @max_time_stamp datetime
declare @temp_product_type varchar(50)
declare @parent_product_id int 

set @pfcursor_deallocate_status = 0
select @temp_product_type = isnull(p_type,'CHIP') from product where Part_Number =  @mfgPartNumber and p_marketing_Part_Number is not null	

if( @temp_product_type = 'CHIP' )
begin
	select @temp_process_step_id = ProcessStep_ID from Product where Part_Number =  @mfgPartNumber and p_marketing_Part_Number is not null
end
else
begin
	select @parent_product_id = P_Parent_Product_ID  from product where Part_Number = @mfgPartNumber and p_marketing_Part_Number is not null
	
	if( @parent_product_id = -1)
	begin	
		select @temp_process_step_id = ProcessStep_ID from Product where Part_Number =  @mfgPartNumber and p_marketing_Part_Number is not null
	end
	else		
	begin
		select @temp_process_step_id = processStep_ID from product where Product_id = ( select P_Parent_Product_ID from product where Part_Number = @mfgPartNumber and p_marketing_Part_Number is not null )
	end
end	

-- The above code is commented by ravikanth on 30 Jan 2004 because the test lots are not needed
-- for cavium because the test can be at any position

	set @tempCursor = CURSOR for
	select lt.Lot_ID, lt.Lot_Number, lt.Fab_lot_Number, lt.Location_ID, lt.Lot_Date_Code from Lot_Table lt where ProcessStep_ID = @temp_process_step_id and ( Lot_Current_Status = 'Running' or   lot_id in (select lot_id from customer_shipping_lot_number_table where cslit_id = @cslit_id ) ) order by location_id
			
	open @tempCursor
	fetch next from @tempCursor
	into @temp_lot_id, @temp_lot_number, @temp_fab_lot_number, @temp_location_id, @temp_lot_date_Code
		while @@fetch_status = 0
		begin
	
			select @max_time_stamp = max(timestamp) from Inventory_Lot where Lot_ID = @temp_lot_id
			if @max_time_stamp is null
			begin
				select @temp_qty_in = IL_Qty_In, @temp_date_in = IL_Date_In, @temp_bal_qty = IL_Qty_Balance from Inventory_Lot where Lot_ID = @temp_lot_id
			end

			else
			begin
				select @temp_qty_in = IL_Qty_In, @temp_date_in = IL_Date_In, @temp_bal_qty = IL_Qty_Balance from Inventory_Lot where Lot_ID = @temp_lot_id and timestamp = @max_time_stamp
			end

			if @temp_bal_qty is null
			begin
				set @temp_bal_qty = @temp_qty_in
			end

			set @temp_qty_pull = -1
			select @temp_qty_pull = CSLNT_Qty_Pull from customer_shipping_lot_number_table cslnt where cslit_id = @cslit_id and Lot_ID = @temp_lot_id

			select @temp_location = (select v.V_Name + '/'+ a.City from Location l, Vendor v, Address a where l.Location_ID = @temp_location_id and l.Vendor_ID = v.Vendor_ID and l.Address_ID = a.Address_ID)

			set @reserved_quantity = 0
			select @reserved_quantity = lrt_lot_quantity from lot_reservation_table lrt, customer_open_order_lineitem cool where cool.lineItem_id = @lineItem_id and cool.cool_line_item_no = lrt.lrt_line_item_no and cool.salesOrder_Id = lrt.lrt_salesOrder_id and lrt.lrt_lot_number = @temp_lot_number

			if @temp_qty_in = -1
			begin
				update @results set lot_id = -1 where lot_id = -1
			end
	
			else
			begin
				insert into @results( Lot_Number, Fab_lot_Number, Lot_ID, Location_ID, Location, Lot_Date_Code, Step_Name, Date_In, Qty_In, Balance_Qty, Reserved_Qty, Quantity_Pull) values( @temp_lot_number, @temp_fab_lot_number, @temp_lot_id, @temp_location_id, @temp_location, @temp_lot_date_code, @process_type_name, @temp_date_in, @temp_qty_in, @temp_bal_qty, @reserved_quantity, @temp_qty_pull)
			end
			fetch next from @tempCursor
			into @temp_lot_id, @temp_lot_number,@temp_fab_lot_number, @temp_location_id, @temp_lot_date_Code
		end
		close @tempCursor 
		deallocate @tempCursor
		select *from @results
		return

